package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.FacturaDao;
import com.sophossystems.client.asmon.dto.FacturaDTO;
import com.sophossystems.client.asmon.dto.FacturaPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.Factura;

public class FacturaDaoImpl extends AbstractDAO implements  FacturaDao
{
	
	private static Logger log = Logger.getLogger(FacturaDaoImpl.class);
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	
	@Override
	@Transactional
	public FacturaPkDTO insert(final FacturaDTO dto){
		FacturaPkDTO exito =null ;
	try {
		 exito = (FacturaPkDTO) getHibernateTemplate().execute(new HibernateCallback() {
			/**
			 * {@inheritDoc}
			 * 
			 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
			 */
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
			    Transaction tx = session.beginTransaction();
			    FacturaPkDTO pk = null;
			    Factura factura = new Factura();
			    factura.mapper(dto);
			    session.save(factura);
			    tx.commit();
			    
			    try{
			    	FacturaPkDTO res = findLastInsert();
			    	pk = new FacturaPkDTO();
			    	if(res != null){
			    		pk.setNumero(res.getNumero());
			    	}else{
			    		pk.setNumero(0);
			    	}
			    	
			    }catch(DaoException es){
			    	throw new HibernateException("Insersion: " +es.getMessage());
			    }
				return pk;
			
				
			}

			
		});
	///	 return exito;
	} catch (final Exception e) {
		logger.error("Error: getActivity, cause: " + e.getMessage(), e);
		
	}
	return exito;
}

		
	/** 
	 * Updates a single row in the Activity table.
	 */
	
	@Override
	@Transactional
	public void update(final FacturaDTO dto) throws DaoException
	{
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					FacturaPkDTO pk = null;
					FacturaDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = findByPrimaryKey(dto
								.getIdCliente());
						pk = new FacturaPkDTO();
						pk.setNumero(res.getNumero());
					} catch (DaoException es) {
						throw new HibernateException(
								"Update FacturaDao: "
										+ es.getMessage());
					}
					///aqui se actualizar los campos y con los de la entrada  
					//cuidado de actualiza todo.
					Factura persis = new Factura();
					persis.mapper(dto);
					persis.setIdCliente(res.getIdCliente());
					session.update(persis);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: update Factura, cause: " + e.getMessage(), e);
			throw new DaoException("Error: update Factura , cause: " + e.getMessage());
		}
	
		
	
	}

	/** 
	 * Deletes a single row in the Activity table.
	 */
	
	@Override
	@Transactional
	public void delete(final FacturaPkDTO pk) throws DaoException
	{
		
		
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
				    Transaction tx = session.beginTransaction();
				    Factura factura = (Factura)session.load(Factura.class,pk.getNumero() );
				    session.delete(factura);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: " + e.getMessage());
		}
	}

	

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "Factura";
	}

	/** 
	 * Returns all rows from the FACTURA table that match the criteria 'numero = :numero'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public FacturaDTO findByPrimaryKey(final int numero) 
			throws DaoException	{
		FacturaDTO factura = null;
		try {
			factura = (FacturaDTO)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
							FacturaDTO factura = null;
							// TODO Auto-generated method stub
							Query oQuery = session.createQuery("SELECT f.numero as numero, f.idCliente as idCliente," +
									" f.concepto as concepto, f.monto as monto, f.fecha as fecha," +
									" f.requiereFactura as requiereFactura, f.facturarA as facturarA FROM " + getTableName() +
									" f WHERE f.numero = :numero");
							oQuery.setInteger("numero", numero);
							List<FacturaDTO> facturaDto = oQuery.setResultTransformer(Transformers.aliasToBean(
									FacturaDTO.class)).list();
							if(facturaDto != null && facturaDto.size() > 0){
								factura = facturaDto.get(0);
							}else{
								
							}
							return factura;
						}
					});
//			= jdbcTemplate.query("SELECT numero, idCliente, concepto, monto, fecha FROM " + getTableName() + " WHERE numero = ?", this,numero);
//			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			logger.error("Error: FACTURA, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return factura;
		
	}

	/** 
	 * Returns all rows from the FACTURA table that match the criteria ''.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<FacturaDTO> findAll() throws DaoException
	{
		List<FacturaDTO> factura = null;
		try {
			factura = (List<FacturaDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
							// TODO Auto-generated method stub
							Query oQuery = session.createQuery("SELECT f.numero as numero, f.idCliente as idCliente," +
									" f.concepto as concepto, f.monto as monto, " +
									" f.fecha as fechaf.requiereFactura as requiereFactura, f.facturarA as a facturarAFROM " + getTableName() + 
									" f ORDER BY f.numero");		
							List<FacturaDTO> facturaDto = oQuery.setResultTransformer(Transformers.aliasToBean(
									Factura.class)).list();
							return facturaDto;
						}
					});
//			return jdbcTemplate.query("SELECT numero, idCliente, concepto, monto, fecha FROM " + getTableName() + " ORDER BY numero", this);
		}
		catch (Exception e) {
			logger.error("Error: FACTURA, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return factura;
	}

	/** 
	 * Returns all rows from the FACTURA table that match the criteria 'numero = :numero'.
	 */
	
	@Override
	@Transactional
	public List<FacturaDTO> findWhereNumeroEquals(final int numero) 
			throws DaoException	{
		List<FacturaDTO> lista = new ArrayList<FacturaDTO>();
		FacturaDTO factura = this.findByPrimaryKey(numero);
		lista.add(factura);	
		
		return lista;
	
//			return jdbcTemplate.query("SELECT numero, idCliente, concepto, monto, fecha FROM " + getTableName() + " WHERE numero = ? ORDER BY numero", this,numero);
	
		
	}

	/** 
	 * Returns all rows from the FACTURA table that match the criteria 'idCliente = :idCliente'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<FacturaDTO> findWhereIdClienteEquals(final int idCliente)
			throws DaoException	{
		List<FacturaDTO> factura = null;
		try {
			factura = (List<FacturaDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
							// TODO Auto-generated method stub
							Query oQuery = session.createQuery("SELECT f.numero as numero, f.idCliente as idCliente," +
									" f.concepto as concepto, f.monto as monto, f.fecha as fecha," +
									" f.requiereFactura as requiereFactura, f.facturarA as facturarA FROM " + getTableName() + 
									" f WHERE f.idCliente = :id_cliente ORDER BY f.idCliente");
							oQuery.setInteger("id_cliente", idCliente);
							List<FacturaDTO> facturaDto = oQuery.setResultTransformer(Transformers.aliasToBean(
									FacturaDTO.class)).list();
							return facturaDto;
						}
					});
//			return jdbcTemplate.query("SELECT numero, idCliente, concep/to, monto, fecha FROM " + getTableName() + " WHERE idCliente = ? ORDER BY idCliente", this,idCliente);
		}
		catch (Exception e) {
			logger.error("Error: FACTURA, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return factura;
	}

	/** 
	 * Returns all rows from the FACTURA table that match the criteria 'concepto = :concepto'.
	
	@Transactional
	public List<FacturaDTO> findWhereConceptoEquals(String concepto) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT numero, idCliente, concepto, monto, fecha FROM " + getTableName() + " WHERE concepto = ? ORDER BY concepto", this,concepto);
		}
		catch (Exception e) {
			logger.error("Error: FACTURA, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	} */

	/** 
	 * Returns all rows from the FACTURA table that match the criteria 'monto = :monto'.
	 
	@Transactional
	public List<FacturaDTO> findWhereMontoEquals(String monto) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT numero, idCliente, concepto, monto, fecha FROM " + getTableName() + " WHERE monto = ? ORDER BY monto", this,monto);
		}
		catch (Exception e) {
			logger.error("Error: FACTURA, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	} */

	/** 
	 * Returns all rows from the FACTURA table that match the criteria 'fecha = :fecha'.
	 
	@Transactional
	public List<FacturaDTO> findWhereFechaEquals(Date fecha) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT numero, idCliente, concepto, monto, fecha FROM " + getTableName() + " WHERE fecha = ? ORDER BY fecha", this,fecha);
		}
		catch (Exception e) {
			logger.error("Error: FACTURA, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		
	} */

	/** 
	 * Returns the rows from the FACTURA table that matches the specified primary-key value.
	 
	public FacturaDTO findByPrimaryKey(FacturaPkDTO pk) throws DaoException
	{
		return findByPrimaryKey( pk.getNumero() );
	} */

	@Override
	public FacturaPkDTO findLastInsert() throws DaoException {
		FacturaPkDTO factura = null;
		try {
			factura = (FacturaPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							Query oQuery = session
									.createQuery("SELECT max(f.numero) as numero FROM "
											+ getTableName()
											+ " f ");
							
							FacturaPkDTO facturaDto = (FacturaPkDTO) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(FacturaPkDTO.class)).list().get(0);
							return facturaDto;
						}
					});
		
		} catch (Exception e) {
			logger.error("Error: FacturaDaoImpl, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return factura;

	}
	
}
